package rsj.service;

import com.rlyy.aibase.db.Dbink;
import com.rlyy.aibase.utils.base.MapNode;
import com.rlyy.aibase.utils.base.Node;
import com.rlyy.aibase.utils.base.TableNode;
import com.rlyy.aibase.utils.base.ThinkException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import rsj.api.org.importdata.ImportEasyExcelInvoke;
import rsj.api.org.importdata.entity.ImportFailedParam;
import rsj.db.PostgresqlConn;
import common.entity.PageInfo;
import common.util.QueryRunnerFactory;

import java.sql.Connection;
import java.util.Objects;

/**
 * 机构管理-查询导入结构和失败数据
 *
 * @author yuanqinglong
 * @since 2021/3/9 16:44
 */
@Component
public class QueryImportFailureData {

    private static final Logger logger = LoggerFactory.getLogger(QueryImportFailureData.class);

    /**
     * 查询导入失败结果信息
     *
     * @param importFailedParam 请求参数
     * @return 详情
     */
    public MapNode detailsOfFailedData(ImportFailedParam importFailedParam) {
        MapNode returnData;
        Connection connection = PostgresqlConn.getConnection();
        try {
            MapNode failedQueries = this.numberOfFailedQueries(connection, importFailedParam);
            MapNode successfulQueries = this.numberOfSuccessfulQueries(connection, importFailedParam);
            MapNode importFailures = this.queryDetailedListOfImportFailures(connection, importFailedParam);
            returnData = new MapNode().putMapNode("Table", importFailures);
            returnData.append(failedQueries);
            returnData.append(successfulQueries);
        } catch (Exception e) {
            logger.error("查询导入失败详情异常", e);
            throw new ThinkException("服务器忙");
        } finally {
            Dbink.close(connection);
        }
        return returnData;
    }


    /**
     * 查询导入失败的详细列表
     *
     * @param connection        数据库连接
     * @param importFailedParam 参数
     * @return 列表
     */
    private MapNode queryDetailedListOfImportFailures(Connection connection, ImportFailedParam importFailedParam) {
        String detailsListSql = this.queryFailureDetailsListSql(importFailedParam);
        TableNode tableNode = QueryRunnerFactory.getQueryRunner().queryLinkTableNode(connection, detailsListSql);
        Node node = QueryRunnerFactory.getQueryRunner().queryLinkNode(connection, this.queryFailureDetailsCountSql(importFailedParam));
        return new MapNode().putNode("List", tableNode.toJson()).putNode("Count", node.value());
    }


    /**
     * 查询本次失败详情列表SQL
     *
     * @return sql
     */
    private String queryFailureDetailsListSql(ImportFailedParam importFailedParam) {
        PageInfo pageInfo = importFailedParam.getPageInfo();
        return "SELECT log_id,log_reqsernumber,log_content, log_fail_res FROM sys.oper_test_fail_log WHERE log_is_ignore = '" + ImportEasyExcelInvoke.NOT_IGNORE +
                "' AND log_org_id = '" + importFailedParam.getOrgId() + "' ORDER BY log_storagetime DESC LIMIT " + pageInfo.getPageSize() + " OFFSET " + pageInfo.getPageOffSet();
    }


    /**
     * 查询失败详情统计个数SQL
     *
     * @return sql
     */
    private String queryFailureDetailsCountSql(ImportFailedParam importFailedParam) {
        return "SELECT COUNT(*) FROM sys.oper_test_fail_log WHERE log_is_ignore = '" + ImportEasyExcelInvoke.NOT_IGNORE + "' AND log_org_id = '" + importFailedParam.getOrgId() + "'";
    }


    /**
     * 查询历史和本次失败个数
     *
     * @param connection        数据库连接
     * @param importFailedParam 参数
     * @return 失败个数
     */
    private MapNode numberOfFailedQueries(Connection connection, ImportFailedParam importFailedParam) {
        String sql = "SELECT his_failed ,this_failed FROM " +
                "(SELECT COUNT(*) AS his_failed FROM sys.oper_test_fail_log WHERE log_org_id ='" + importFailedParam.getOrgId() + "') A, ";
        if (Objects.nonNull(importFailedParam.getImportTime())) {
            sql += "(SELECT COUNT(*) AS this_failed FROM sys.oper_test_fail_log" +
                    " WHERE log_storagetime = '" + importFailedParam.getImportTime() +
                    "' AND log_org_id ='" + importFailedParam.getOrgId() + "') B";
        } else {
            sql += "(SELECT '0' AS this_failed) B";
        }
        MapNode mapNode;
        try {
            mapNode = QueryRunnerFactory.getQueryRunner().queryLinkRow(connection, sql);
        } catch (ThinkException e) {
            logger.error("查询历史和本次失败个数、SQL:{}", sql, e);
            throw new ThinkException("服务器忙");
        }
        return mapNode;
    }


    /**
     * 查询历史和本次成功个数
     *
     * @param connection        数据库连接
     * @param importFailedParam 参数
     * @return 成功个数
     */
    private MapNode numberOfSuccessfulQueries(Connection connection, ImportFailedParam importFailedParam) {
        String sql = "SELECT his_success ,this_success FROM " +
                "(SELECT COUNT(*) AS his_success FROM sys.oper_test_log WHERE log_user_id = (SELECT aut_keyid FROM sys.sys_org LEFT JOIN " +
                "sys.sys_authon ON org_id = aut_org_id WHERE org_id ='" + importFailedParam.getOrgId() + "')) A, ";
        if (Objects.isNull(importFailedParam.getImportTime())) {
            sql += "(SELECT '0' AS this_success) B";
        } else {
            sql += "(SELECT COUNT(*) AS this_success FROM sys.oper_test_log " +
                    " WHERE log_storagetime = '" + importFailedParam.getImportTime() +
                    "' AND log_user_id = (SELECT aut_keyid FROM sys.sys_org LEFT JOIN sys.sys_authon " +
                    "ON org_id = aut_org_id WHERE org_id ='" + importFailedParam.getOrgId() + "')) B";
        }
        MapNode mapNode;
        try {
            mapNode = QueryRunnerFactory.getQueryRunner().queryLinkRow(connection, sql);
        } catch (ThinkException e) {
            logger.error("查询历史和本次成功个数异常、SQL:{}", sql, e);
            throw new ThinkException("服务器忙");
        }
        return mapNode;
    }


}
